﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace CRM.UTIL
{
    #region  SQLServer2005
    /// <summary>
    /// DBChannel 的摘要说明。

    /// </summary>
    public class DBChannel : IDisposable
    {
        protected SqlCommand sqlCmd;
        private bool disposed = false;

        ~DBChannel()
        {
            this.Dispose(false);
        }
        public DBChannel()
        {

            //this.sqlCmd = new SqlCommand("", new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CRM"]));
            string strCn = System.Configuration.ConfigurationManager.AppSettings["CRM"].ToString();
            SqlConnection cn = new SqlConnection(strCn);
            sqlCmd = new SqlCommand("", cn);
            this.sqlCmd.CommandTimeout = 900;//默认 15 分钟
        }

        /// <summary>
        /// 使用指定 ID 的数据库连接字串
        /// </summary>
        /// <param name="conId">存放在 Web.config 中的数据库连接字串 ID</param>
        public DBChannel(string conId)
        {
            conId = (conId == "") ? "CRM" : conId;
            this.sqlCmd = new SqlCommand("", new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.AppSettings[conId]));
            this.sqlCmd.CommandTimeout = 900;//默认 15 分钟
        }

        /// <summary>
        ///  回收资源，释放内存

        /// </summary>
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        /// <summary>
        ///	注销变量
        /// </summary>
        protected virtual void Dispose(bool disposing)
        {
            if (!this.disposed)
            {
                //if (disposing)
                if (this.sqlCmd != null)
                {
                    if (this.sqlCmd.Connection != null) //注销数据库的连接
                    {
                        this.CloseConnection();
                        sqlCmd.Connection.Dispose();
                    }
                    this.sqlCmd.Dispose();
                }
                this.sqlCmd = null;
            }
            this.disposed = true;
        }

        /// <summary>
        /// 关闭数据库的连接
        /// </summary>
        protected void CloseConnection()
        {
            if (this.sqlCmd.Connection.State == System.Data.ConnectionState.Open)
                this.sqlCmd.Connection.Close();//关闭数据连接
        }

        /// <summary>
        /// 获取多表的数据 DataSet
        /// </summary>
        /// <param name="cmdText">执行的字串</param>
        private DataSet GetDataSet(string cmdText)
        {
            DataSet ds = new DataSet();

            this.sqlCmd.CommandText = cmdText;

            System.Data.SqlClient.SqlDataAdapter da = new SqlDataAdapter(this.sqlCmd);
            da.Fill(ds);

            this.CloseConnection();
            return ds;
        }

        /// <summary>
        /// 运行普通 SQL 语句，获取数据集 DataSet
        /// </summary>
        /// <param name="sql"> SQL 语句</param>
        public DataSet GetDataSetBySql(string sql)
        {
            this.sqlCmd.CommandType = System.Data.CommandType.Text;
            return this.GetDataSet(sql);
        }

        /// <summary>
        /// 使用存储过程获取数据集 DataSet
        /// </summary>
        /// <param name="proName">存储过程名称</param>
        public DataSet GetDataSetByProcedure(string proName)
        {
            this.sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            return this.GetDataSet(proName);
        }

        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="cmdText">执行的字串</param>
        private DataTable GetData(string cmdText)
        {
            DataTable dt = new DataTable();

            this.sqlCmd.CommandText = cmdText;

            System.Data.SqlClient.SqlDataAdapter da = new SqlDataAdapter(this.sqlCmd);
            da.Fill(dt);

            this.CloseConnection();
            return dt;
        }

        /// <summary>
        /// 运行普通 SQL 语句，获取数据集 DataTable
        /// </summary>
        /// <param name="sql"> SQL 语句</param>
        public DataTable GetDataBySql(string sql)
        {
            this.sqlCmd.CommandType = System.Data.CommandType.Text;
            return this.GetData(sql);
        }

        /// <summary>
        /// 使用存储过程获取数据集 DataTable
        /// </summary>
        /// <param name="proName">存储过程名称</param>
        public DataTable GetDataByProcedure(string proName)
        {
            this.sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            return this.GetData(proName);
        }

        /// <summary>
        /// 执行语句，并返回受影响的行数
        /// </summary>
        /// <param name="sql"> SQL 语句</param>
        /// <returns>受影响的行数</returns>
        public int Execute(string sql)
        {
            this.sqlCmd.CommandText = sql;
            if (this.sqlCmd.Connection.State == System.Data.ConnectionState.Closed)
                this.sqlCmd.Connection.Open();
            int i = this.sqlCmd.ExecuteNonQuery();
            this.CloseConnection();
            return i;
        }


        /// <summary>
        /// 执行普通 SQL 语句
        /// </summary>
        /// <param name="sql"> SQL 语句</param>
        /// <returns>受影响的行数</returns>
        protected int ExecuteSQL(string sql)
        {
            this.sqlCmd.CommandType = System.Data.CommandType.Text;
            return this.Execute(sql);
        }

        /// <summary>
        /// 仅执行存储过程，不返回数据集
        /// </summary>
        /// <param name="proName">存储过程名称</param>
        /// <returns>受影响的行数</returns>
        protected int ExecuteProcedure(string proName)
        {
            this.sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            return this.Execute(proName);
        }
    }
    #endregion
}
